package ch.zbw.pra.sysventory.database.jdbcTemplate;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.mysql.jdbc.Statement;

import ch.zbw.pra.sysventory.database.mapper.ComputerMapper;
import ch.zbw.pra.sysventory.model.Computer;

/**
 * Greift auf die Datenbank zu (DB-Tabelle Computer) und delegiert das Mapping der Tabellenspalten an den RowMapper
 * Klasse kann auf die Datenbank schreiben und lesen
 * 
 * @author Roberto Lucas
 */

public class ComputerJDBCTemplate implements Serializable{

	private static final long serialVersionUID = 312239829126768652L;
	private static Logger LOG = LoggerFactory.getLogger(ComputerJDBCTemplate.class);
	private JdbcTemplate jdbcTemplateObject;
	
	private static final String INSERT_SQL = "INSERT INTO Computer (compName, compRAM, compTyp, compSid, comp_scanID_fk) VALUES(?,?,?,?,?)";
	
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplateObject = new JdbcTemplate(dataSource);
	}

	public int insertComputer(final Computer computer, final Integer scanversionId) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{
		jdbcTemplateObject.update(new PreparedStatementCreator() { 
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { 
				PreparedStatement ps = connection.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS); 
					ps.setString(1, computer.getHostNameComputer()); 
					ps.setString(2, computer.getRamComputer()); 
					ps.setString(3, computer.getTypeComputer());
					ps.setString(4, computer.getSidComputer());
					ps.setString(5, String.valueOf(scanversionId)); 
					return ps; 
				} 
			}, keyHolder);
		} catch(DataAccessException e){
			LOG.debug("Failed to insert Computer for ScanVersion "+scanversionId);
			LOG.error(e.toString());
		}
		LOG.debug("Inserted computer with id: " + keyHolder.getKey());
		return keyHolder.getKey().intValue();
	}

	public List<Computer> listComputers() {
		String SQL = "SELECT * FROM Computer GROUP BY compName,compRam," +
				"compTyp,compSid";
		List<Computer> computers = new ArrayList<Computer>();
		try{
			computers = jdbcTemplateObject.query(SQL,
				new ComputerMapper());
		} catch (DataAccessException e){
			LOG.debug("Failed to load a list of Computers");
			LOG.error(e.toString());
		}
		LOG.debug("Loaded a list of Computers");
		return computers;
	}

	public List<Computer> listComputersFromScanversion(Integer scanversionId) {
		String SQL = "SELECT * FROM Computer WHERE comp_scanID_fk = ?";
		List<Computer> computers = new ArrayList<Computer>();
		try{
			computers = jdbcTemplateObject.query(SQL,
				new Object[] { scanversionId }, new ComputerMapper());
		} catch (DataAccessException e){
			LOG.debug("Failed to load a list of Computers for Sanversion "+scanversionId);
			LOG.error(e.toString());
		}
		LOG.debug("Loaded a list of Computers for Scanversion "+scanversionId);
		return computers;
	}
}
